﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_Transfers : IDAL_FIM_Transfers
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_FIM_Transfers()
        {

        }

        public IList<M_FIM_Transfers> GetFull(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq, string transfer_type)
        {
            string strSQL;
            DataTable drc = null;
            IList<M_FIM_Transfers> infoList = new List<M_FIM_Transfers>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@transfer_type", transfer_type);

                strSQL = "SELECT t.transfer_seq, t.bgm_source_seq, t.work_area_seq, t.bgm_year"
                    + ", t.transfer_type, t.transfer_name, t.ref_no, t.ref_no_outside, t.transfer_date"
                    + ", t.from_bank_acc_seq, t.to_bank_acc_seq, t.bank_acc_type_seq"
                    + ", t.amount, t.currency_seq, t.exchange_rate, t.bath_amount"
                    + ", t.emp_seq, t.tran_grp_seq"

                    + ", s.bgm_source_name, area.work_area_name, cur.currency_name"
                    + ", acc1.bank_acc_no as from_bank_acc_no, acc1.bank_acc_name as from_bank_acc_name"
                    + ", acc2.bank_acc_no as to_bank_acc_no, acc2.bank_acc_name as to_bank_acc_name"
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name)as emp_text"

                    + ", t.approved_by, t.approved_when, t.record_status, rs.record_status_name"
                    + ", t.created_by, t.created_when"
                    + ", t.updated_by, t.updated_when"
                    + " FROM fim_transfers t"
                    + " LEFT JOIN bgm_mas_source s ON t.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc1 ON t.from_bank_acc_seq = acc1.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc2 ON t.to_bank_acc_seq = acc2.bank_acc_seq"
                    + " LEFT JOIN fim_mas_currencies cur ON t.currency_seq = cur.currency_seq"

                    + " LEFT JOIN hrm_ps_employees emp ON t.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON t.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON t.record_status = rs.record_status"
                    + " WHERE t.record_status not like @delete_status"
                    + (bgm_source_seq != null ? " and t.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " and t.bgm_year = @bgm_year" : "")
                    + (work_area_seq != null ? " and t.work_area_seq = @work_area_seq" : "")
                    + (transfer_type != null ? " and t.transfer_type = @transfer_type" : "");
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (drc.Rows.Count > 0)
                {
                    for (int i = 0; i < drc.Rows.Count; i++)
                    {
                        M_FIM_Transfers info = new M_FIM_Transfers();
                        info.transfer_seq = Convert.ToInt32(drc.Rows[i]["transfer_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc.Rows[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc.Rows[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc.Rows[i]["bgm_year"]);
                        info.transfer_type = drc.Rows[i]["transfer_type"].ToString();
                        info.transfer_name = drc.Rows[i]["transfer_name"].ToString();
                        info.ref_no = drc.Rows[i]["ref_no"].ToString();
                        info.ref_no_outside = drc.Rows[i]["ref_no_outside"].ToString();
                        info.transfer_date = cn.ConvertNullDateTime(drc.Rows[i]["transfer_date"]);
                        info.from_bank_acc_seq = cn.ConvertNullInt(drc.Rows[i]["from_bank_acc_seq"]);
                        info.to_bank_acc_seq = cn.ConvertNullInt(drc.Rows[i]["to_bank_acc_seq"]);
                        info.bank_acc_type_seq = cn.ConvertNullInt(drc.Rows[i]["bank_acc_type_seq"]);
                        info.amount = cn.ConvertNullDouble(drc.Rows[i]["amount"]);
                        info.currency_seq = cn.ConvertNullInt(drc.Rows[i]["currency_seq"]);
                        info.exchange_rate = cn.ConvertNullDouble(drc.Rows[i]["exchange_rate"]);
                        info.bath_amount = cn.ConvertNullDouble(drc.Rows[i]["bath_amount"]);
                        info.emp_seq = cn.ConvertNullInt(drc.Rows[i]["emp_seq"]);
                        info.tran_grp_seq = cn.ConvertNullInt(drc.Rows[i]["tran_grp_seq"]);

                        info.bgm_source_name = drc.Rows[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc.Rows[i]["work_area_name"].ToString();
                        info.currency_name = drc.Rows[i]["currency_name"].ToString();
                        info.from_bank_acc_no = drc.Rows[i]["from_bank_acc_no"].ToString();
                        info.from_bank_acc_name = drc.Rows[i]["from_bank_acc_name"].ToString();
                        info.to_bank_acc_no = drc.Rows[i]["to_bank_acc_no"].ToString();
                        info.to_bank_acc_name = drc.Rows[i]["to_bank_acc_name"].ToString();
                        info.emp_text = drc.Rows[i]["emp_text"].ToString();

                        info.approved_by = cn.ConvertNullInt(drc.Rows[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc.Rows[i]["approved_when"]);

                        info.record_status = drc.Rows[i]["RECORD_STATUS"].ToString();
                        info.record_status_name = drc.Rows[i]["RECORD_STATUS_NAME"].ToString();
                        info.created_by = cn.ConvertNullInt(drc.Rows[i]["CREATED_BY"]);
                        info.created_when = cn.ConvertNullDateTime(drc.Rows[i]["CREATED_WHEN"]);
                        info.updated_by = cn.ConvertNullInt(drc.Rows[i]["UPDATED_BY"]);
                        info.updated_when = cn.ConvertNullDateTime(drc.Rows[i]["UPDATED_WHEN"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Transfers> GetList(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> work_area_seq, string transfer_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Transfers> infoList = new List<M_FIM_Transfers>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(4, "@transfer_type", transfer_type);

                strSQL = "SELECT transfer_seq"
                    + ", concat(coalesce(ref_no, ''), ' - ', coalesce(transfer_name, '')) as transfer_name"
                    + " FROM fim_transfers"
                    + " WHERE record_status not like @delete_status"
                    + (bgm_source_seq != null ? " and bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " and bgm_year = @bgm_year" : "")
                    + (work_area_seq != null ? " and work_area_seq = @work_area_seq" : "")
                    + (transfer_type != null ? " and transfer_type = @transfer_type" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Transfers info = new M_FIM_Transfers();
                        info.transfer_seq = Convert.ToInt32(drc[i]["transfer_seq"]);
                        info.transfer_name = drc[i]["transfer_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Transfers> GetFullFromCheque(int? cheque_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Transfers> infoList = new List<M_FIM_Transfers>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cheque_seq", cheque_seq);

                strSQL = "SELECT t.transfer_seq, t.bgm_source_seq, t.work_area_seq, t.bgm_year"
                    + ", t.transfer_type, t.transfer_name, t.ref_no, t.ref_no_outside, t.transfer_date"
                    + ", t.from_bank_acc_seq, t.to_bank_acc_seq, t.bank_acc_type_seq"
                    + ", t.amount, t.currency_seq, t.exchange_rate, t.bath_amount"
                    + ", t.emp_seq, t.tran_grp_seq, t.cheque_seq, t.pay_seq"

                    //+ ", s.bgm_source_name, area.work_area_name, cur.currency_name"
                    //+ ", acc1.bank_acc_no as from_bank_acc_no, acc1.bank_acc_name as from_bank_acc_name"
                    //+ ", acc2.bank_acc_no as to_bank_acc_no, acc2.bank_acc_name as to_bank_acc_name"
                    //+ ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name)as emp_text"

                    + ", t.approved_by, t.approved_when, t.record_status"
                    + ", t.created_by, t.created_when"
                    + ", t.updated_by, t.updated_when"
                    + " FROM fim_transfers t"
                    //+ " LEFT JOIN bgm_mas_source s ON t.bgm_source_seq = s.bgm_source_seq"
                    //+ " LEFT JOIN fim_mas_bank_accounts acc1 ON t.from_bank_acc_seq = acc1.bank_acc_seq"
                    //+ " LEFT JOIN fim_mas_bank_accounts acc2 ON t.to_bank_acc_seq = acc2.bank_acc_seq"
                    //+ " LEFT JOIN fim_mas_currencies cur ON t.currency_seq = cur.currency_seq"

                    //+ " LEFT JOIN hrm_ps_employees emp ON t.emp_seq = emp.emp_seq"
                    //+ " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    //+ " LEFT JOIN hrm_mp_work_areas area ON t.work_area_seq = area.work_area_seq"
                    //+ " LEFT JOIN sys_record_status rs ON t.record_status = rs.record_status"
                    + " WHERE t.record_status not like @delete_status"
                    + (cheque_seq != null ? " and t.cheque_seq = @cheque_seq" : "");


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Transfers info = new M_FIM_Transfers();
                        info.transfer_seq = Convert.ToInt32(drc[i]["transfer_seq"]);
                        info.bgm_source_seq = Convert.ToInt32(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = Convert.ToInt32(drc[i]["work_area_seq"]);
                        info.bgm_year = Convert.ToInt32(drc[i]["bgm_year"]);
                        info.transfer_type = drc[i]["transfer_type"].ToString();
                        info.transfer_name = drc[i]["transfer_name"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.ref_no_outside = drc[i]["ref_no_outside"].ToString();
                        info.transfer_date = cn.ConvertNullDateTime(drc[i]["transfer_date"]);
                        info.from_bank_acc_seq = cn.ConvertNullInt(drc[i]["from_bank_acc_seq"]);
                        info.to_bank_acc_seq = cn.ConvertNullInt(drc[i]["to_bank_acc_seq"]);
                        info.bank_acc_type_seq = cn.ConvertNullInt(drc[i]["bank_acc_type_seq"]);
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.currency_seq = cn.ConvertNullInt(drc[i]["currency_seq"]);
                        info.exchange_rate = cn.ConvertNullDouble(drc[i]["exchange_rate"]);
                        info.bath_amount = cn.ConvertNullDouble(drc[i]["bath_amount"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        info.cheque_seq = cn.ConvertNullInt(drc[i]["cheque_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);

                        //info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        //info.work_area_name = drc[i]["work_area_name"].ToString();
                        //info.currency_name = drc[i]["currency_name"].ToString();
                        //info.from_bank_acc_no = drc[i]["from_bank_acc_no"].ToString();
                        //info.from_bank_acc_name = drc[i]["from_bank_acc_name"].ToString();
                        //info.to_bank_acc_no = drc[i]["to_bank_acc_no"].ToString();
                        //info.to_bank_acc_name = drc[i]["to_bank_acc_name"].ToString();
                        //info.emp_text = drc[i]["emp_text"].ToString();

                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);

                        info.record_status = drc[i]["RECORD_STATUS"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["CREATED_BY"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["CREATED_WHEN"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["UPDATED_BY"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["UPDATED_WHEN"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_Transfers GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Transfers info = new M_FIM_Transfers();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT t.transfer_seq, t.bgm_source_seq, t.work_area_seq, t.bgm_year"
                    + ", t.transfer_type, t.transfer_name, t.ref_no, t.ref_no_outside, t.transfer_date"
                    + ", t.from_bank_acc_seq, t.to_bank_acc_seq, t.bank_acc_type_seq"
                    + ", t.amount, t.currency_seq, t.exchange_rate, t.bath_amount"
                    + ", t.emp_seq, t.tran_grp_seq, t.cheque_seq, t.pay_seq"

                    + ", s.bgm_source_name, area.work_area_name, cur.currency_name"
                    + ", acc1.bank_acc_no as from_bank_acc_no, acc1.bank_acc_name as from_bank_acc_name"
                    + ", acc2.bank_acc_no as to_bank_acc_no, acc2.bank_acc_name as to_bank_acc_name"
                    //+ "
                    + ", concat(np.prefix_name, emp.first_name, ' ', emp.last_name)as emp_text"

                    + ", t.record_status, rs.record_status_name"
                    + " FROM fim_transfers t"
                    + " LEFT JOIN bgm_mas_source s ON t.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc1 ON t.from_bank_acc_seq = acc1.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc2 ON t.to_bank_acc_seq = acc2.bank_acc_seq"
                    + " LEFT JOIN fim_mas_currencies cur ON t.currency_seq = cur.currency_seq"

                    + " LEFT JOIN hrm_ps_employees emp ON t.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON t.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON t.record_status = rs.record_status"
                    + " WHERE t.transfer_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.transfer_seq = Convert.ToInt32(drc[0]["transfer_seq"]);
                    info.bgm_source_seq = Convert.ToInt32(drc[0]["bgm_source_seq"]);
                    info.work_area_seq = Convert.ToInt32(drc[0]["work_area_seq"]);
                    info.bgm_year = Convert.ToInt32(drc[0]["bgm_year"]);
                    info.transfer_type = drc[0]["transfer_type"].ToString();
                    info.transfer_name = drc[0]["transfer_name"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.ref_no_outside = drc[0]["ref_no_outside"].ToString();
                    info.transfer_date = cn.ConvertNullDateTime(drc[0]["transfer_date"]);
                    info.from_bank_acc_seq = cn.ConvertNullInt(drc[0]["from_bank_acc_seq"]);
                    info.to_bank_acc_seq = cn.ConvertNullInt(drc[0]["to_bank_acc_seq"]);
                    info.bank_acc_type_seq = cn.ConvertNullInt(drc[0]["bank_acc_type_seq"]);
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.currency_seq = cn.ConvertNullInt(drc[0]["currency_seq"]);
                    info.exchange_rate = cn.ConvertNullDouble(drc[0]["exchange_rate"]);
                    info.bath_amount = cn.ConvertNullDouble(drc[0]["bath_amount"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);
                    info.cheque_seq = cn.ConvertNullInt(drc[0]["cheque_seq"]);
                    info.pay_seq = cn.ConvertNullInt(drc[0]["pay_seq"]);

                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.from_bank_acc_no = drc[0]["from_bank_acc_no"].ToString();
                    info.from_bank_acc_name = drc[0]["from_bank_acc_name"].ToString();
                    info.to_bank_acc_no = drc[0]["to_bank_acc_no"].ToString();
                    info.to_bank_acc_name = drc[0]["to_bank_acc_name"].ToString();
                    info.emp_text = drc[0]["emp_text"].ToString();

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.record_status_name = drc[0]["RECORD_STATUS_NAME"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_Transfers info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@transfer_seq", info.transfer_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(4, "@transfer_type", info.transfer_type);
                dbManager.AddParameters(5, "@transfer_name", info.transfer_name);
                dbManager.AddParameters(6, "@ref_no", info.ref_no);
                dbManager.AddParameters(7, "@ref_no_outside", info.ref_no_outside);
                dbManager.AddParameters(8, "@transfer_date", info.transfer_date);
                dbManager.AddParameters(9, "@from_bank_acc_seq", info.from_bank_acc_seq);
                dbManager.AddParameters(10, "@to_bank_acc_seq", info.to_bank_acc_seq);
                dbManager.AddParameters(11, "@bank_acc_type_seq", info.bank_acc_type_seq);
                dbManager.AddParameters(12, "@amount", info.amount);
                dbManager.AddParameters(13, "@currency_seq", info.currency_seq);
                dbManager.AddParameters(14, "@exchange_rate", info.exchange_rate);
                dbManager.AddParameters(15, "@bath_amount", info.bath_amount);
                dbManager.AddParameters(16, "@emp_seq", info.emp_seq);

                dbManager.AddParameters(17, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(18, "@created_by", info.created_by);
                dbManager.AddParameters(19, "@created_when", info.created_when);
                dbManager.AddParameters(20, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(21, "@pay_seq", info.pay_seq);

                strSQL = "INSERT INTO fim_transfers"
                    + " (bgm_source_seq, work_area_seq, bgm_year"
                    + ", transfer_type, transfer_name, ref_no, ref_no_outside, transfer_date"
                    + ", from_bank_acc_seq, to_bank_acc_seq, bank_acc_type_seq, amount"
                    + ", currency_seq, exchange_rate, bath_amount, emp_seq, cheque_seq, pay_seq"
                    + " , record_status, created_by, created_when)"
                    + " VALUES(@bgm_source_seq"
                    + ", @work_area_seq"
                    + ", @bgm_year"
                    + ", @transfer_type"
                    + ", @transfer_name"
                    + ", @ref_no"
                    + ", @ref_no_outside"
                    + ", @transfer_date"
                    + ", @from_bank_acc_seq"
                    + ", @to_bank_acc_seq"
                    + ", @bank_acc_type_seq"
                    + ", @amount"
                    + ", @currency_seq"
                    + ", @exchange_rate"
                    + ", @bath_amount"
                    + ", @emp_seq"
                    + ", @cheque_seq"
                    + ", @pay_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " );"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_Transfers info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(21);

                dbManager.AddParameters(0, "@transfer_seq", info.transfer_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(4, "@transfer_type", info.transfer_type);
                dbManager.AddParameters(5, "@transfer_name", info.transfer_name);
                dbManager.AddParameters(6, "@ref_no", info.ref_no);
                dbManager.AddParameters(7, "@ref_no_outside", info.ref_no_outside);
                dbManager.AddParameters(8, "@transfer_date", info.transfer_date);
                dbManager.AddParameters(9, "@from_bank_acc_seq", info.from_bank_acc_seq);
                dbManager.AddParameters(10, "@to_bank_acc_seq", info.to_bank_acc_seq);
                dbManager.AddParameters(11, "@bank_acc_type_seq", info.bank_acc_type_seq);
                dbManager.AddParameters(12, "@amount", info.amount);
                dbManager.AddParameters(13, "@currency_seq", info.currency_seq);
                dbManager.AddParameters(14, "@exchange_rate", info.exchange_rate);
                dbManager.AddParameters(15, "@bath_amount", info.bath_amount);
                dbManager.AddParameters(16, "@emp_seq", info.emp_seq);

                dbManager.AddParameters(17, "@updated_by", info.updated_by);
                dbManager.AddParameters(18, "@updated_when", info.updated_when);
                dbManager.AddParameters(19, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(20, "@pay_seq", info.pay_seq);

                strSQL = "UPDATE fim_transfers"
                    + " set bgm_source_seq = @bgm_source_seq"
                    + ", work_area_seq = @work_area_seq"
                    + ", bgm_year = @bgm_year"
                    + ", transfer_type = @transfer_type"
                    + ", transfer_name = @transfer_name"
                    + ", ref_no = @ref_no"
                    + ", ref_no_outside = @ref_no_outside"
                    + ", transfer_date = @transfer_date"
                    + ", from_bank_acc_seq = @from_bank_acc_seq"
                    + ", to_bank_acc_seq = @to_bank_acc_seq"
                    + ", bank_acc_type_seq = @bank_acc_type_seq"
                    + ", amount = @amount"
                    + ", currency_seq = @currency_seq"
                    + ", exchange_rate = @exchange_rate"
                    + ", bath_amount = @bath_amount"
                    + ", emp_seq = @emp_seq"
                    + ", cheque_seq = @cheque_seq"
                    + ", pay_seq = @pay_seq"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE transfer_seq = @transfer_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_Transfers info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@transfer_seq", info.transfer_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_transfers"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE transfer_seq = @transfer_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
